﻿using System;
using System.Linq;
using System.Text;
using Dapper;
using Dapper.Contrib.Extensions;

namespace Topro.Application.Impl
{
    using System.Collections.Generic;
    using Topro.Application.Interface;
    using Topro.Db;
    using Topro.Entity;
    using Topro.Entity.Query;
    using Topro.Entity.Request.IQS;
    using Topro.Entity.Response.IQS;
    using Topro.Entity.Tables;
    using Topro.Log;

    /// <summary>
    /// IQS业务接口实现
    /// </summary>
    public class IQSServiceImpl: IQSService
    {
        #region 注入
        /// <summary>
        /// db
        /// </summary>
        private readonly IDb _db;

        /// <summary>
        /// loggerService
        /// </summary>
        private readonly LoggerService _loggerService;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dapperFactory"></param>
        /// <param name="logger"></param>
        public IQSServiceImpl(IDb db, LoggerService loggerService)
        {
            _db = db;
            _loggerService = loggerService;
        }
        #endregion

        /// <summary>
        /// 获取IQS
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public IQSResponse GetIqs(int id)
        {
            var iqs = _db.DbConnection.Query<IQSResponse>("select * from iqs where Id=@Id", new { Id = id }).FirstOrDefault();

            return iqs;
        }

        /// <summary>
        /// 保存、修改
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult SaveIQS(SaveIQSRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };


            try
            {
                if (request.Id > 0)
                {
                    var iqs = _db.DbConnection.Query<IQS>("select * from iqs where Id=@Id", new { Id = request.Id }).FirstOrDefault();
                    if (iqs == null)
                    {
                        throw new Exception("IQS记录不存在.");
                    }

                    //iqs.PlntNum = int.Parse(request.PlntNum);
                    iqs.StaffNum = request.StaffNum;
                    iqs.ShiftNum = request.ShiftNum;
                    //iqs.EquipmentCode = request.EquipmentCode;
                    //iqs.LotBatch = request.LotBatch;
                    //iqs.PartNum = request.PartNum;
                    //iqs.HeapNumber = request.HeapNumber;
                    //iqs.MaterialType = request.Status;
                    //iqs.BarCode = request.BarCode;
                    //iqs.PressProgram = request.PressProgram;
                    //iqs.CuFoilNum = request.CuFoilNum;
                    //iqs.CuFoilBatch = request.CuFoilBatch;
                    //iqs.CuFoilExpire = request.CuFoilExpire;
                    //iqs.PpTypeNumber = request.PpTypeNumber;
                    //iqs.PpNumber = request.PpNumber;
                    //iqs.PpPcfPartNum1 = request.PpPcfPartNum1;
                    //iqs.PpPcfBatchNum1 = request.PpPcfBatchNum1;
                    //iqs.PpPcfExpire1 = request.PpPcfExpire1;
                    //iqs.PpPcfPartNum2 = request.PpPcfPartNum2;
                    //iqs.PpPcfBatchNum2 = request.PpPcfBatchNum2;
                    //iqs.PpPcfExpire2 = request.PpPcfExpire2;
                    //iqs.PlaeType = request.PlaeType;
                    //iqs.PlateNum = request.PlateNum;
                    iqs.PanelQuan = Convert.ToInt32(request.PanelQuan);
                    //iqs.SecionQuan = request.SecionQuan;
                    //iqs.RotateLayup = request.RotateLayup;
                    //iqs.Remark = request.Remark;
                    //iqs.Status = request.Status;
                    //iqs.CreatedBy = request.CreatedBy;
                    //iqs.TravellerDate = Convert.ToDateTime(request.TravellerDate);
                    //iqs.MaterialDate = Convert.ToDateTime(request.MaterialDate);
                    //iqs.CreatedDate = Convert.ToDateTime(request.CreatedDate);

                    _db.DbConnection.Update<IQS>(iqs);

                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Message = @$"修改Book成功.编号:{request.Id},员工号:{iqs.StaffNum},班次:{iqs.ShiftNum},板数:{iqs.PanelQuan}",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });

                    response.Success = true;
                }
                else
                {
                    throw new Exception("操作失败.");
                }
            }
            catch(Exception ex)
            {
                response.Message = ex.Message;
            }
            
            return response;
        }

        /// <summary>
        /// 保存、修改
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult AdminSaveIQS(SaveIQSRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };


            try
            {
                if (request.Id > 0)
                {
                    var iqs = _db.DbConnection.Query<IQS>("select * from iqs where Id=@Id", new { Id = request.Id }).FirstOrDefault();
                    if (iqs == null)
                    {
                        throw new Exception("IQS记录不存在.");
                    }

                    iqs.MachineCode = request.MachineCode;
                    iqs.PlntNum = request.PlntNum;
                    iqs.EquipmentCode = request.EquipmentCode;
                    iqs.LotBatch = request.LotBatch;
                    iqs.PartNum = request.PartNum;
                    iqs.HeapNumber = request.HeapNumber;
                    iqs.TravellerDate = Convert.ToDateTime(request.TravellerDate);
                    iqs.PressProgram = request.PressProgram;
                    iqs.MaterialDate = Convert.ToDateTime(request.MaterialDate);
                    iqs.CreatedDate = Convert.ToDateTime(request.CreatedDate);

                    _db.DbConnection.Update<IQS>(iqs);

                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Message = @$"修改Book成功.编号:{request.Id},员工号:{iqs.StaffNum},班次:{iqs.ShiftNum},板数:{iqs.PanelQuan}",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });

                    response.Success = true;
                }
                else
                {
                    throw new Exception("操作失败.");
                }
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <returns></returns>
        public JResult UpdateStatus(UpdateIQSRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var iqs = _db.DbConnection.QueryFirst<IQS>("select * from iqs where Id=@Id", new { Id = request.Id });
                if (iqs == null)
                {
                    throw new Exception("记录不存在.");
                }

                var updateSql = "UPDATE iqs SET DeleteStatus=@DeleteStatus WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { DeleteStatus = 1, Id = request.Id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                //日志
                _loggerService.Add(new OperLog()
                {
                    Message = @$"删除Book成功.{request.Id}",
                    Exception = "",
                    Ip = request.Ip,
                    CreatedBy = request.UserId
                });

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 更新员工号
        /// </summary>
        /// <returns></returns>
        public JResult UpdateStaffNum(UpdateIQSRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<IQS>("select * from iqs where Id=@Id", new { Id = request.Id });
                if (user == null)
                {
                    throw new Exception("记录不存在.");
                }

                var updateSql = "UPDATE iqs SET StaffNum=@StaffNum WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { StaffNum = request.StaffNum, Id = request.Id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                //日志
                _loggerService.Add(new OperLog()
                {
                    Message = @$"更新员工号成功.{request.Id}",
                    Exception = "",
                    Ip = request.Ip,
                    CreatedBy = request.UserId
                });

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<IQSQueryResponse> GetIqsPager(IQSQuery query)
        {
            var pageIndex = query.Page <= 0 ? 0 : (query.Page.Value - 1) * query.Rows.Value;

            var files = @"iqs.*,
(SELECT sn.Text FROM ShiftNum sn WHERE sn.Value=iqs.ShiftNum) AS ShiftNumString,
(SELECT sq.Text FROM StopperQuality sq WHERE sq.Value=iqs.StopperQuality) AS StopperQualityString,
(SELECT psq.Text FROM PerpregSurfaceQuality psq WHERE psq.Value=iqs.PerpregSurfaceQuality) AS PerpregSurfaceQualityString,
(SELECT pkq.Text FROM PcsKraftQuality pkq WHERE pkq.Value=iqs.PcsKraftQuality) AS PcsKraftQualityString,
(SELECT icq.Text FROM InnerCoreQuality icq WHERE icq.Value=iqs.InnerCoreQuality) AS InnerCoreQualityString,
(SELECT cfq.Text FROM CopperFoilQuality cfq WHERE cfq.Value=iqs.CopperFoilQuality) AS CopperFoilQualityString";
            var tableName = "iqs iqs";
            var orderby = "ORDER BY iqs.Id DESC";
            var where = "where 1=1";

            #region 条件
            //编号
            if (query.Id.HasValue)
            {
                where += string.Format(" And iqs.Id = {0}", query.Id);
            }
            //厂号
            if (query.PlntNum>0)
            {
                where += string.Format(" And iqs.PlntNum like '%{0}%'", query.PlntNum);
            }
            //员工号
            if (!string.IsNullOrEmpty(query.StaffNum))
            {
                where += string.Format(" And iqs.StaffNum like '%{0}%'", query.StaffNum);
            }
            //批号
            if (!string.IsNullOrEmpty(query.LotBatch))
            {
                where += string.Format(" And iqs.LotBatch like '%{0}%'", query.LotBatch);
            }
            //料号
            if (!string.IsNullOrEmpty(query.PartNum))
            {
                where += string.Format(" And iqs.PartNum like '%{0}%'", query.PartNum);
            }
            //设备编码
            if (!string.IsNullOrEmpty(query.EquipmentCode))
            {
                where += string.Format(" And iqs.EquipmentCode like '%{0}%'", query.EquipmentCode);
            }
            //拆板人
            if(!string.IsNullOrEmpty(query.WatchmanBy))
            {
                where += string.Format(" And iqs.WatchmanBy like '%{0}%'", query.WatchmanBy);
            }
            //时间
            if(!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And iqs.CreatedDate BETWEEN '{0}' AND '{1}'", query.StartTime,query.EndTime);
            }
            //删除状态
            if (!string.IsNullOrEmpty(query.DeleteStatus))
            {
                where += " And iqs.DeleteStatus=0";
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.Limit);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.Rows.Value);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var rows = multi.Read<IQSQueryResponse>();

                return new DataGrid<IQSQueryResponse>()
                {
                    Total = count,
                    Rows = rows,
                    //Footer = footers,
                    Success = true
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<IQSQueryResponse> GetOptIqsPager(IQSQuery query)
        {
            var pageIndex = query.Page <= 0 ? 0 : (query.Page.Value - 1) * query.Rows.Value;

            var files = @"iqs.*,
(SELECT sn.Text FROM ShiftNum sn WHERE sn.Value=iqs.ShiftNum) AS ShiftNumString,
(SELECT sq.Text FROM StopperQuality sq WHERE sq.Value=iqs.StopperQuality) AS StopperQualityString,
(SELECT psq.Text FROM PerpregSurfaceQuality psq WHERE psq.Value=iqs.PerpregSurfaceQuality) AS PerpregSurfaceQualityString,
(SELECT pkq.Text FROM PcsKraftQuality pkq WHERE pkq.Value=iqs.PcsKraftQuality) AS PcsKraftQualityString,
(SELECT icq.Text FROM InnerCoreQuality icq WHERE icq.Value=iqs.InnerCoreQuality) AS InnerCoreQualityString,
(SELECT cfq.Text FROM CopperFoilQuality cfq WHERE cfq.Value=iqs.CopperFoilQuality) AS CopperFoilQualityString";
            var tableName = "iqs iqs";
            var orderby = "ORDER BY iqs.Id DESC";
            var where = "where 1=1";

            #region 条件
            //编号
            if (query.Id.HasValue)
            {
                where += string.Format(" And iqs.Id = {0}", query.Id);
            }
            #endregion

            var sb = new StringBuilder();

            var rows = new List<IQSQueryResponse>();

            //sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.Limit);
            //sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.Rows.Value);

            var model = _db.DbConnection.Query<IQSQueryResponse>(string.Format("SELECT {0} FROM {1} {2} {3}", files, tableName, where, orderby)).FirstOrDefault();

            if (model != null && query.UserPermissions.Any(c=>c.Contains("IQSOpt0002")))
            {
                model.EditPermission = "IQSOpt0002";
            }

            rows.Add(model);

            return new DataGrid<IQSQueryResponse>()
            {
                Total = 1,
                Rows = rows,
                //Footer = footers,
                Success = true
            };
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<IQSQueryResponse> Pager(IQSQuery query)
        {
            var pageIndex = query.Page <= 0 ? 0 : (query.Page.Value - 1) * query.Rows.Value;

            var files = @"iqs.*,
(SELECT sn.Text FROM ShiftNum sn WHERE sn.Value=iqs.ShiftNum) AS ShiftNumString,
(SELECT sq.Text FROM StopperQuality sq WHERE sq.Value=iqs.StopperQuality) AS StopperQualityString,
(SELECT psq.Text FROM PerpregSurfaceQuality psq WHERE psq.Value=iqs.PerpregSurfaceQuality) AS PerpregSurfaceQualityString,
(SELECT pkq.Text FROM PcsKraftQuality pkq WHERE pkq.Value=iqs.PcsKraftQuality) AS PcsKraftQualityString,
(SELECT icq.Text FROM InnerCoreQuality icq WHERE icq.Value=iqs.InnerCoreQuality) AS InnerCoreQualityString,
(SELECT cfq.Text FROM CopperFoilQuality cfq WHERE cfq.Value=iqs.CopperFoilQuality) AS CopperFoilQualityString";
            var tableName = "iqs iqs";
            var orderby = "ORDER BY iqs.Id DESC";
            var where = "where 1=1";

            #region 条件
            //编号
            if (query.Id.HasValue)
            {
                where += string.Format(" And iqs.Id = {0}", query.Id);
            }
            //厂号
            if (query.PlntNum > 0)
            {
                where += string.Format(" And iqs.PlntNum like '%{0}%'", query.PlntNum);
            }
            //员工号
            if (!string.IsNullOrEmpty(query.StaffNum))
            {
                where += string.Format(" And iqs.StaffNum like '%{0}%'", query.StaffNum);
            }
            //批号
            if (!string.IsNullOrEmpty(query.LotBatch))
            {
                where += string.Format(" And iqs.LotBatch like '%{0}%'", query.LotBatch);
            }
            //料号
            if (!string.IsNullOrEmpty(query.PartNum))
            {
                where += string.Format(" And iqs.PartNum like '%{0}%'", query.PartNum);
            }
            //设备编码
            if (!string.IsNullOrEmpty(query.EquipmentCode))
            {
                where += string.Format(" And iqs.EquipmentCode like '%{0}%'", query.EquipmentCode);
            }
            //拆板人
            if (!string.IsNullOrEmpty(query.WatchmanBy))
            {
                where += string.Format(" And iqs.WatchmanBy like '%{0}%'", query.WatchmanBy);
            }
            //时间
            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And iqs.CreatedDate BETWEEN '{0}' AND '{1}'", query.StartTime, query.EndTime);
            }
            //删除状态
            if (!string.IsNullOrEmpty(query.DeleteStatus))
            {
                where += " And iqs.DeleteStatus=0";
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.Limit);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.Rows.Value);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var rows = multi.Read<IQSQueryResponse>();

                var panelQuan = rows.Sum(p=>p.PanelQuan);

                //定义页脚
                var footers = new List<IQSQueryResponse>() {

                    new IQSQueryResponse()
                    {
                        Id = "总计",
                        PanelQuan = panelQuan,
                        UseStatus = -1,
                        CreatedDate = null
                    }

                    //new IQSQueryResponse()
                    //{

                    //    ProxyMerchantName = "总计",
                    //    _DepositAmount = depositAmountTotal.ToString("#0.00"),
                    //    _PayAmount = payTotal.ToString("#0.00"),
                    //    _JieSuanAmount = jieSuanTotal.ToString("#0.00"),
                    //    _LiRunAmount = liRunTotal.ToString("#0.00"),
                    //}
                };
                return new DataGrid<IQSQueryResponse>()
                {
                    Total = count,
                    Rows = rows,
                    Footer = footers,
                    Success = true
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<IQSQueryResponse> GetIqsExport(IQSQuery query)
        {
            //var pageIndex = query.Page <= 0 ? 0 : (query.Page - 1) * query.Limit;

            var files = @"iqs.*,
(SELECT sn.Text FROM ShiftNum sn WHERE sn.Value=iqs.ShiftNum) AS ShiftNumString,
(SELECT sq.Text FROM StopperQuality sq WHERE sq.Value=iqs.StopperQuality) AS StopperQualityString,
(SELECT psq.Text FROM PerpregSurfaceQuality psq WHERE psq.Value=iqs.PerpregSurfaceQuality) AS PerpregSurfaceQualityString,
(SELECT pkq.Text FROM PcsKraftQuality pkq WHERE pkq.Value=iqs.PcsKraftQuality) AS PcsKraftQualityString,
(SELECT icq.Text FROM InnerCoreQuality icq WHERE icq.Value=iqs.InnerCoreQuality) AS InnerCoreQualityString,
(SELECT cfq.Text FROM CopperFoilQuality cfq WHERE cfq.Value=iqs.CopperFoilQuality) AS CopperFoilQualityString";
            var tableName = "iqs iqs";
            var orderby = "ORDER BY iqs.Id DESC";
            var where = "where 1=1";

            #region 条件
            //厂号
            if (query.PlntNum > 0)
            {
                where += string.Format(" And iqs.PlntNum like '%{0}%'", query.PlntNum);
            }
            //员工号
            if (!string.IsNullOrEmpty(query.StaffNum))
            {
                where += string.Format(" And iqs.StaffNum like '%{0}%'", query.StaffNum);
            }
            //批号
            if (!string.IsNullOrEmpty(query.LotBatch))
            {
                where += string.Format(" And iqs.LotBatch like '%{0}%'", query.LotBatch);
            }
            //料号
            if (!string.IsNullOrEmpty(query.PartNum))
            {
                where += string.Format(" And iqs.PartNum like '%{0}%'", query.PartNum);
            }
            //设备编码
            if (!string.IsNullOrEmpty(query.EquipmentCode))
            {
                where += string.Format(" And iqs.EquipmentCode like '%{0}%'", query.EquipmentCode);
            }
            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And iqs.CreatedDate BETWEEN '{0}' AND '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("select {0} from {1} {2} {3}", files, tableName, where, orderby);

            var list = _db.DbConnection.Query<IQSQueryResponse>(sb.ToString());

            return list;
        }
    }
}
